
import path from 'path';
import fs from 'fs';
import process from 'child_process';
import moment from 'moment';
import SqlString from 'sqlstring';

import {
    getUserListByOpenids,
    getShareDeviceQRCode,
    upgradeNotification,
    sendNewMemberMessage,
    sendUnbindMessage
} from './wx_handle';

import {
    getTicket,
    getWxAccessToken,
    getWechatPublicPlatformById,
    getWechatIdBySn,
} from '../controllers/wx_token';

import {
    API_CODE_SERVER_ERR,
    API_CODE_TOKEN_MISSING,
    API_CODE_PUBLIC_PLATFORM_NOT_EXISTS,
    API_CODE_SIGNATURE_FAILED,
    API_CODE_NOT_FIND_DEVICE_BY_IP,
} from '../config/api_code.js';
import {
    firstBindDevice,
    multipleBindDevice
} from '../biz/common_biz';
import { exesql } from '../database/db';
import { setAlbumGroup, getAlbumGroup } from '../database/redis_db';
import { sendLogToEmail } from '../controllers/logger';
import { sqlInterceptor, getCategoryList } from './utils';

import { LIMIT_MSECONDS, APP_VERSION_CODE, LIKE_PREFIX } from '../config/config';


/**
 * 微信接口
 */
class Common {
    constructor() {

        this.query = this.query.bind(this);
        this.getSignature = this.getSignature.bind(this);

        this.bindNewDevice = this.bindNewDevice.bind(this);
        this.getBindUsers = this.getBindUsers.bind(this);
        this.unBindDevice = this.unBindDevice.bind(this);
        this.updateBindDeviceInfo = this.updateBindDeviceInfo.bind(this);

        this.getPublicPlatform = this.getPublicPlatform.bind(this);
        this.shareDeviceQRCode = this.shareDeviceQRCode.bind(this);
        this.notifyUpgrade = this.notifyUpgrade.bind(this);
        this.sendUpgrdeNotification = this.sendUpgrdeNotification.bind(this);

        this.removeBindUser = this.removeBindUser.bind(this);
        this.getBindDevice = this.getBindDevice.bind(this);
        this.getDeviceSnByIp = this.getDeviceSnByIp.bind(this);
        this.getSoundWavesAudio = this.getSoundWavesAudio.bind(this);
        this.deleteSoundWavesAudio = this.deleteSoundWavesAudio.bind(this);

        // 获取首页数据
        this.getAlbumGroup = this.getAlbumGroup.bind(this);
        this.getHomeRankAlbum = this.getHomeRankAlbum.bind(this);
        this.getRankAlbumPage = this.getRankAlbumPage.bind(this);
        this.getAlbumList = this.getAlbumList.bind(this);
        this.getAlbumGroupById = this.getAlbumGroupById.bind(this);
        this.getAudios = this.getAudios.bind(this);
        this.getUserInfo = this.getUserInfo.bind(this);
        this.getAlbumInfo = this.getAlbumInfo.bind(this);

        // 我的教材专辑
        this.primaryAlbumList = this.primaryAlbumList.bind(this);

        // 收藏
        this.addAlbum = this.addAlbum.bind(this);
        this.removeAlbum = this.removeAlbum.bind(this);
        this.storeAudio = this.storeAudio.bind(this);
        this.removeStoreAudio = this.removeStoreAudio.bind(this);
        this.favoriteCount = this.favoriteCount.bind(this);
        this.categoryCount = this.categoryCount.bind(this);
        this.favoriteList = this.favoriteList.bind(this);
        this.storeCategoryAudioList = this.storeCategoryAudioList.bind(this);
        // 删除我的收藏音频
        this.removeFavorite = this.removeFavorite.bind(this);
        this.removeStoreCategory = this.removeStoreCategory.bind(this);
        // 查询类别下的所有音频
        this.findCategoryAudioList = this.findCategoryAudioList.bind(this);
    }


    // 操作数据库
    async query(req, res) {
        const { sql, params } = req.body;
        try {
            if (!sqlInterceptor(sql)) {
                res.apiError(API_CODE_SERVER_ERR, 'only select statement');
                return;
            }
            const { err, result } = await exesql(sql, params);
            if (err) {
                res.apiError(err.errno, err);
                sendLogToEmail(err);
                return;
            };
            res.apiSuccess(result);
        } catch (err) {
            res.apiError(API_CODE_SERVER_ERR, err);
            sendLogToEmail(err);
        }
    }

    getWxToken(wechat_id) {
        return new Promise(function (resolve, reject) {
            getWxAccessToken(wechat_id, (token) => {
                resolve(token);
            });
        })
    }

    // 签名
    async getSignature(req, res) {
        const { wechat_id, url } = req.body;
        try {
            const token = await this.getWxToken(wechat_id);
            if (!token) {
                res.apiError(API_CODE_TOKEN_MISSING, 'token不存在');
                return;
            }
            const config = getWechatPublicPlatformById(wechat_id);
            if (!config) {
                res.apiError(API_CODE_PUBLIC_PLATFORM_NOT_EXISTS, '公众号配置信息不存在');
                return;
            }
            const result = await getTicket(config.app_id, token, url);
            if (result) {
                res.apiSuccess(result);
            } else {
                res.apiError(API_CODE_SIGNATURE_FAILED, '签名失败');
            }
        } catch (error) {
            sendLogToEmail('签名失败:' + error);
            res.apiError(API_CODE_SERVER_ERR, error);
        }
    }

    // 配网绑定新设备,强制性调用百度接口进行绑定
    async bindNewDevice(req, res) {
        const { device_id, openid, nick_name } = req.body;
        try {
            const { err, result } = await exesql('SELECT device_id FROM device  WHERE device.sn=? LIMIT 1;', [device_id]);
            if (err) {
                res.apiError(API_CODE_SERVER_ERR, err);
                return;
            }
            // 第一次绑定
            if (result.length === 0) {
                const ip = this.getClientIp(req);
                firstBindDevice(device_id, openid, nick_name, ip, (err) => {
                    if (err) {
                        res.apiError(API_CODE_SERVER_ERR, err);
                        sendLogToEmail(err);
                        return;
                    }
                    res.apiSuccess();
                    const wechatId = getWechatIdBySn(device_id);
                    getWxAccessToken(wechatId, (wxToken) => {
                        sendNewMemberMessage(wxToken, device_id, openid);
                    });
                });

            } else {
                // 已绑定
                console.log(`已绑定:${device_id}，openid:${openid}`);
                multipleBindDevice(device_id, openid, function (err) {
                    if (err) {
                        res.apiError(API_CODE_SERVER_ERR, err);
                        sendLogToEmail(err);
                        return;
                    }
                    res.apiSuccess();
                    const wechatId = getWechatIdBySn(device_id);
                    getWxAccessToken(wechatId, (wxToken) => {
                        sendNewMemberMessage(wxToken, device_id, openid);
                    });
                });
            }

        } catch (err) {
            res.apiError(API_CODE_SERVER_ERR, err);
            sendLogToEmail(err);
        }
    }


    // 获取绑定设备的所有用户
    async getBindUsers(req, res) {
        const { device_sn } = req.body;
        const sql = 'SELECT `open_id` FROM `device_openid` WHERE `device_sn` = ?;';
        try {
            const { err, result } = await exesql(sql, [device_sn]);
            if (err) {
                res.apiError(API_CODE_SERVER_ERR, err);
                sendLogToEmail(err);
                return;
            }
            if (result.length === 0) {
                res.apiSuccess([]);
                return;
            }
            let userList = [];
            result.forEach(item => {
                userList.push({
                    "openid": item.open_id,
                    "lang": "zh_CN"
                });
            });
            const wechatId = getWechatIdBySn(device_sn);
            const token = await this.getWxToken(wechatId);
            if (token === '') {
                res.apiError(API_CODE_TOKEN_MISSING, `wechatId:${wechatId}, 获取不到token`);
                sendLogToEmail(`wechatId:${wechatId}, 获取不到token`);
                return;
            }
            const data = await getUserListByOpenids(token, userList);
            if (data.err) {
                res.apiError(API_CODE_SERVER_ERR, data.err);
                sendLogToEmail(data.err);
                return;
            }
            res.apiSuccess(data.list);
        } catch (err) {
            res.apiError(API_CODE_SERVER_ERR, err);
            sendLogToEmail(err);
        }
    }

    // 获取绑定的设备信息
    async getBindDevice(req, res) {
        const { openid } = req.body;
        try {
            if (!openid || openid === 'undefined') {
                const ip = this.getClientIp(req);
                const ua = req.get('User-Agent');
                const errMsg = `[${ip}] getBindDeviceInfo openid:${openid},ua;${ua}`;
                console.log(errMsg);
                res.apiSuccess();
                return;
            }
            let sql = 'SELECT device_openid.device_sn ,device.uuid, device.device_id,device_uuid_init_permission.permission_group_id FROM  device_openid,device  LEFT JOIN device_uuid_init_permission ON device_uuid_init_permission.uuid = device.uuid WHERE open_id = ? AND device_openid.device_sn = device.sn LIMIT 1';
            const { err, result } = await exesql(sql, [openid]);
            if (err) {
                res.apiError(API_CODE_SERVER_ERR, err);
                sendLogToEmail(err);
                return;
            }
            if (result.length === 0) {
                res.apiSuccess();
                return;
            }

            const sn = result[0].device_sn;
            sql = 'SELECT `access_token` FROM `device_uuid_list` WHERE `device_id`= ? LIMIT 1; ';
            const ret = await exesql('SELECT `access_token` FROM `device_uuid_list` WHERE `device_id`= ? LIMIT 1; ', [sn]);
            if (ret.err) {
                sendLogToEmail(ret.err);
                res.apiError(API_CODE_SERVER_ERR, ret.err.stack);
                return;
            }
            if (ret.result.length === 0) {
                const errMsg = `设备序列号:${sn}获取不到百度device_uuid_list中的access_token`;
                sendLogToEmail(errMsg);
                res.apiError(API_CODE_SERVER_ERR,errMsg);
                return;
            }
            const device = Object.assign(result[0], { wechat_id: getWechatIdBySn(sn),token:ret.result[0].access_token});
            res.apiSuccess(device);

        } catch (err) {
            res.apiError(API_CODE_SERVER_ERR, err.stack);
            sendLogToEmail(err);
        }
    }

    // 根据ip获取还未绑定的设备信息
    async getDeviceSnByIp(req, res) {
        const ip = this.getClientIp(req);
        const date = new Date((new Date().getTime() - LIMIT_MSECONDS));
        const formatTime = moment(date).format('YYYY-MM-DD HH:mm:ss.SSS');
        console.log(`微信ip:${ip}, time:${formatTime}`);
        const sql = 'SELECT device_online_log.sn,device.sn as device_sn FROM device_online_log LEFT JOIN device ON device.sn = device_online_log.sn  WHERE device_online_log.ip = ? AND device_online_log.log_time >= ? ;';
        try {
            const { err, result } = await exesql(sql, [ip, formatTime]);
            if (err) {
                sendLogToEmail(err);
                res.apiError(API_CODE_SERVER_ERR, error);
                return;
            }
            if (result.length === 0) {
                console.log(`发现五分钟内,ip:${ip} 未找到设备`);
                res.apiError(API_CODE_NOT_FIND_DEVICE_BY_IP, `发现五分钟内,ip:${ip} 未找到设备`);
                return;
            }
            // 过滤重复的
            console.log('过滤前:', result)
            const filterList = this.unique(result).filter(item => {
                return item.device_sn === null;
            });
            console.log('过滤后:', filterList);
            // 5 min 找到多台设备
            if (filterList.length === 0) {
                console.log(`过滤后,ip:${ip}未找到设备`);
                res.apiError(API_CODE_SERVER_ERR, `过滤后,ip:${ip}未找到设备`);
                return;
            }
            if (filterList.length > 1) {
                console.log(`发现五分钟内,同一个ip:${ip}有多台设备`);
                res.apiError(API_CODE_SERVER_ERR, `发现五分钟内,同一个ip:${ip}有多台设备`);
                return;
            }
            const sn = filterList[0].sn;
            console.log(`###### ip:${ip} -> ${sn}`);
            res.apiSuccess(sn);

        } catch (error) {
            sendLogToEmail(error);
            res.apiError(API_CODE_SERVER_ERR, error);
        }
    }

    unique(array) {
        array.sort();
        var re = [array[0]];
        for (var i = 1; i < array.length; i++) {
            if (array[i].sn !== re[re.length - 1].sn) {
                re.push(array[i]);
            }
        }
        return re;
    }

    // 解绑设备
    async unBindDevice(req, res) {
        // FIXME:事务处理
        console.log(`>>>>>`)
        const { device_sn, open_id } = req.body;
        const sql = 'DELETE FROM `device_openid` WHERE `device_sn` = ? AND `open_id` = ? ;';
        try {
            const { err } = await exesql(sql, [device_sn, open_id]);
            if (err) {
                res.apiError(API_CODE_SERVER_ERR, err);
                sendLogToEmail(err);
                return;
            }
            console.log(`unBindDevice`);
            res.apiSuccess();
            const wechatId = getWechatIdBySn(device_sn);
            const wxToken = await this.getWxToken(wechatId);
            sendUnbindMessage(wxToken, device_sn, open_id, false);
        } catch (err) {
            res.apiError(API_CODE_SERVER_ERR, err);
            sendLogToEmail(err);
        }

    }

    // 更新绑定设备信息
    async updateBindDeviceInfo(req, res) {
        const { device_id, nick_name, brithday, gender, avatar_url } = req.body;
        const sql = 'UPDATE device SET device.nick_name = ?, device.brithday = ?, device.gender = ?, device.avatar_url = ? WHERE device.device_id = ?;';
        try {
            const { err, result } = await exesql(sql, [nick_name, brithday, gender, avatar_url, device_id]);
            if (err) {
                console.log(err);
                sendLogToEmail(err);
                res.apiError(API_CODE_SERVER_ERR, err);
                return;
            }
            res.apiSuccess();
        } catch (err) {
            console.log(err);
            sendLogToEmail(err);
            res.apiError(API_CODE_SERVER_ERR, err);

        }
    }


    // 获取公众号信息
    async getPublicPlatform(req, res) {
        const wechat_id = req.query.wechat_id;
        console.log(`公众号 >>> wechat_id：${wechat_id}`);
        const sql = 'SELECT A.app_id,A.device_type,A.device_name,A.company_name,A.bind_img_name, A.album_group_template_id,B.config FROM  wechat_manage AS A LEFT JOIN wechat_config_template as B ON B.wechat_config_template_id = A.wechat_config_template_id  WHERE A.wechat_id = ? LIMIT 1;'
        try {
            const { err, result } = await exesql(sql, [wechat_id]);
            if (err) {
                res.apiError(API_CODE_SERVER_ERR, err);
                sendLogToEmail(err);
                return;
            }
            if (result.length === 0) {
                res.apiError(API_CODE_SERVER_ERR, new Error('not exists public platform'));
            } else {
                let data = result[0];
                Object.assign(data, { app_version_code: APP_VERSION_CODE })
                res.apiSuccess(data);
            }

        } catch (err) {
            res.apiError(API_CODE_SERVER_ERR, err);
            sendLogToEmail(err);
        }
    }


    // 通知升级
    async notifyUpgrade(req, res) {
        const { device_type, new_version } = req.body;
        const sql = "SELECT `device_id`,`sn`,`version`,`uuid`,`chip_id` FROM `device` WHERE `sn` like '" + device_type + "%'";
        try {
            const { err, result } = await exesql(sql, [device_type]);
            if (err) {
                res.apiError(API_CODE_SERVER_ERR, err);
                sendLogToEmail(err);
                return;
            }
            console.log(`****公众号的设备数量*** :${result.length}`);

            const platFromSql = 'SELECT `wechat_id`,`host`,`device_name`,`company_name`,`template_id` FROM `wechat_manage` WHERE `device_type` = ?';
            const wechatData = await exesql(platFromSql, [device_type]);
            if (wechatData.err) {
                res.apiError(API_CODE_SERVER_ERR, err);
                sendLogToEmail(err);
                return;
            }
            if (wechatData.result > 0) {
                const wechatPlatform = wechatData.result[0];
                getWxAccessToken(wechatPlatform.wechat_id, accessToken => {
                    if (accessToken) {
                        this.sendUpgrdeNotification(accessToken, result, wechatPlatform, new_version);
                    }
                });
            }
            res.apiSuccess();
        } catch (err) {
            res.apiError(API_CODE_SERVER_ERR, err);
            sendLogToEmail(err);
        }

    }

    async sendUpgrdeNotification(accessToken, deviceList, wechatPlatform, new_version) {
        const templateUrl = "https://api.weixin.qq.com/cgi-bin/message/template/send?access_token=" + accessToken;
        for (let index = 0; index < deviceList.length; index++) {
            const device = deviceList[index];
            if (device.version <= new_version) {
                continue;
            }
            // 查询出每台设备绑定的所有用户
            const sql = 'SELECT `open_id` FROM `device_openid` WHERE `device_sn` = ? ;';
            const { err, result } = await exesql(sql, [device.sn]);
            if (err) {
                sendLogToEmail(err);
                return;
            }
            if (result.length === 0) {
                continue;
            }
            // 将单个设备的所有用户一起发送
            for (let i = 0; i < result.length; i++) {
                const item = {
                    url: templateUrl,
                    open_id: result[i],
                    device_sn: device.sn,
                    cur_version: device.version,
                    new_version: new_version,
                    uuid: device.uuid,
                    name: wechatPlatform.device_name,
                    template_id: wechatPlatform.template_id
                };
                upgradeNotification(item);
            }
        }
    }

    /**
     * 分享设备的二维码
     */
    async shareDeviceQRCode(req, res) {
        const { device_sn } = req.body;
        const wechatId = getWechatIdBySn(device_sn);
        try {
            const accessToken = await this.getWxToken(wechatId);
            if (accessToken === '') {
                res.apiError(API_CODE_TOKEN_MISSING, `wechatId:${wechatId}, 获取不到token,${device_sn}`);
                sendLogToEmail(`shareDeviceQRCode wechatId:${wechatId}, 获取不到token，${device_sn}`);
                return;
            }
            const { err, result } = await getShareDeviceQRCode(accessToken, device_sn);
            if (err) {
                res.apiError(API_CODE_SERVER_ERR, err);
                sendLogToEmail(err);
                return;
            }
            res.apiSuccess(result);
        } catch (err) {
            res.apiError(API_CODE_SERVER_ERR, err);
            sendLogToEmail(err);
        }
    }


    // 删除绑定设备的用户
    async removeBindUser(req, res) {
        const { device_sn, openid } = req.body;
        const sql = 'DELETE FROM `device_openid` WHERE `device_sn` = ? AND `open_id` = ?';
        try {
            const { err, result } = await exesql(sql, [device_sn, openid]);
            if (err) {
                sendLogToEmail(err);
                return;
            }
            const wechatId = getWechatIdBySn(device_sn);
            const wxToken = await this.getWxToken(wechatId);
            sendUnbindMessage(wxToken, device_sn, openid, true);
            res.apiSuccess();

        } catch (err) {
            res.apiError(API_CODE_SERVER_ERR, err);
            sendLogToEmail(err);
        }
    }

    // 获取声波配网音频
    async getSoundWavesAudio(req, res) {
        const { ssid, password } = req.body;
        const data = "\"" + "@@" + ssid + "##" + password + "\"";
        const fileName = Math.floor(new Date().getTime()) + '.wav';
        const projectPath = path.join(__dirname, '../');
        const audioPath = path.join(projectPath, 'web/uploads/' + fileName);

        console.log(`__dirname:${__dirname}`);
        console.log(`audioPath:${audioPath}`);

        process.exec(projectPath + 'command/ADTCommand ' + data + ' ' + audioPath, function (error, stdout, stderr) {
            if (error) {
                sendLogToEmail('ssid:' + ssid + '>>>' + error.stack);
                res.apiError(-1, JSON.stringify(error));
            } else {
                res.apiSuccess(fileName)
            }
        })
    }

    // 删除声波配网音频
    async deleteSoundWavesAudio(req, res) {
        const name = req.body.file_name;
        const serverPath = path.join(__dirname, '../');
        const filePath = path.join(serverPath, 'web/uploads/' + name);
        console.log(`delete:${filePath}`);
        fs.exists(filePath, function (exists) {
            if (!exists) {
                fs.unlink(filePath, function (err) {
                    if (err) {
                        console.error(`文件删除失败:`, err);
                    }
                });
            }
            res.apiSuccess();
        })

    }


    getClientIp(req) {
        let ipAddress = req.headers['x-forwarded-for'] ||
            req.connection.remoteAddress ||
            req.socket.remoteAddress ||
            req.connection.socket.remoteAddress;
        if (ipAddress.split(',').length > 0) {
            ipAddress = ipAddress.split(',')[0]
        }
        let arr = ipAddress.split(':')
        let ip = arr[arr.length - 1]
        return ip;
    };

    // 获取专辑组
    async getAlbumGroup(req, res) {
        const { album_group_template_id, permission_group_id } = req.body;
        try {
            if (album_group_template_id === 0) {
                res.apiError(-1, '参数错误, album_group_template_id:' + album_group_template_id);
                return;
            }
            const data = await getAlbumGroup(album_group_template_id, permission_group_id);
            const albumGroups = JSON.parse(data);
            if (albumGroups) {
                res.apiSuccess(albumGroups);
                return;
            }

            // 没有带教材的
            let shortSql = ' and (album_permission_group.`permission_group_id` =1 or album_permission_group.`permission_group_id` is null) ';
            if (permission_group_id === -1) {
                shortSql = ' and ( album_permission_group.`permission_group_id` is null) ';
            }
            // 检查redis中是否存在数据
            const sql = 'select album_permission_group.`permission_group_id`, album_group_show.album_group_id,album_group_show.image_name,album_group_show.name as group_name, album_group_show.order_index, album.* ' +
                ' from album_group,album_group_show, album_group_template_group,album left join album_permission_group on (album.album_id = album_permission_group.album_id)' +
                ' where album_group_template_group.album_group_id = album_group.album_group_id ' +
                ' and album.album_id = album_group.album_id ' +
                ' and album_group_show.`album_group_id` = album_group.`album_group_id` ' +
                ' and album_group_template_group.album_group_template_id =? ' + shortSql +
                ' order by album_group_show.order_index, album_group.order_index ';

            console.log(`sql:`, sql);
            const { err, result } = await exesql(sql, [album_group_template_id]);
            if (err) {
                sendLogToEmail(err);
                res.apiError(API_CODE_SERVER_ERR, err);
                return;
            }
            const list = getCategoryList(result);
            console.log(`分组数据:`, list);
            setAlbumGroup(album_group_template_id, permission_group_id, list);
            res.apiSuccess(list);
        } catch (error) {
            res.apiError(API_CODE_SERVER_ERR, error.stack);
        }
    }

    // 首页专辑排名
    async getHomeRankAlbum(req, res) {
        const { uuid, range } = req.body;
        const sql = 'SELECT album.album_id,album.name,album.img_url,album.play_rate,' +
            ' (select count(*) from audio where audio.album_id = album.album_id) as count from album WHERE ' +
            '  album.is_buy = 0 AND album.category_id IN (?) ORDER BY play_rate DESC LIMIT 5;';
        try {
            const { err, result } = await exesql(sql, [range, uuid]);
            if (err) {
                res.apiError(API_CODE_SERVER_ERR, err);
                return;
            }
            res.apiSuccess(result);
        } catch (error) {
            res.apiError(API_CODE_SERVER_ERR, error.stack);
        }
    }

    /**
     * 分页获取排行列表
     */
    async getRankAlbumPage(req, res) {
        const { uuid, play_rate, range } = req.body;
        let value = ' AND album.play_rate < ' + play_rate;
        if (play_rate === -1) {
            value = '';
        }
        const sql = 'SELECT album.album_id,album.name,album.img_url,album.play_rate,' +
            ' (select count(*) from audio where audio.album_id = album.album_id) as count from album ' +
            ' WHERE  album.is_buy = 0 AND album.category_id IN (?) ' + value + ' ORDER BY play_rate DESC LIMIT 20;';

        try {
            const { err, result } = await exesql(sql, [range, uuid]);
            if (err) {
                res.apiError(API_CODE_SERVER_ERR, err);
                return;
            }
            res.apiSuccess(result);

        } catch (error) {
            res.apiError(API_CODE_SERVER_ERR, error.stack);
        }
    }


    // 教材专辑列表
    async primaryAlbumList(req, res) {
        const { uuid } = req.body;
        try {
            const sql = 'SELECT album.album_id,album.name FROM album WHERE album.is_buy = 1 AND album.album_id NOT IN (select album_id from device_album where uuid = ?)';
            const { err, result } = await exesql(sql, [uuid]);
            if (err) {
                res.apiError(API_CODE_SERVER_ERR, err);
                return;
            }
            res.apiSuccess(result);

        } catch (error) {
            res.apiError(API_CODE_SERVER_ERR, error.stack);
        }
    }


    // 主界面专辑列表
    async getAlbumList(req, res) {
        const { category_id, uuid, latest_id } = req.body;
        const sql = SqlString.format('SELECT a.id, a.album_id,a.name,a.img_url, (select count(*) from audio where audio.album_id = a.album_id) as count FROM album as a WHERE a.category_id = ?  AND (a.is_buy = 0  OR (a.is_buy = 1 AND a.album_id in (select device_album.album_id from device_album where device_album.uuid = ?))) AND a.id > ? ORDER BY a.id ASC LIMIT 20', [category_id, uuid, latest_id]);
        try {
            const { err, result } = await exesql(sql);
            if (err) {
                res.apiError(API_CODE_SERVER_ERR, err);
                return;
            }
            res.apiSuccess(result);
        } catch (error) {
            res.apiError(API_CODE_SERVER_ERR, error.stack);
        }
    }
    // 点击更多进入专辑组分页
    async getAlbumGroupById(req, res) {
        const { album_group_id, uuid, latest_id } = req.body;
        const sql = SqlString.format('SELECT album.id,album.album_id,album.name,album.img_url,album.is_buy, (select count(*) from audio where audio.album_id = album.album_id) as count FROM album, album_group WHERE album_group.album_group_id = ? AND album.album_id = album_group.album_id  AND  (album.is_buy = 0  OR (album.is_buy = 1 AND album.album_id in (select device_album.album_id from device_album where device_album.uuid = ?))) AND album.id > ? ORDER BY album.id  LIMIT 20;', [album_group_id, uuid, latest_id]);
        try {
            const { err, result } = await exesql(sql);
            if (err) {
                res.apiError(API_CODE_SERVER_ERR, err);
                return;
            }
            res.apiSuccess(result);
        } catch (error) {
            res.apiError(API_CODE_SERVER_ERR, error.stack);
        }
    }

    // 获取指定专辑下的音频信息
    async getAudios(req, res) {
        const { uuid, album_id, latest_id, count } = req.body;

        const sql = 'SELECT album.category_id, album.name as album_name, audio.album_id,audio.id,audio.audio_id,audio.name,audio.duration,audio.url ' +
            ' FROM album, audio WHERE album.album_id = audio.album_id and audio.album_id=? AND audio.id > ? ORDER BY audio.id LIMIT 20;';
        try {
            const { err, result } = await exesql(sql, [album_id, latest_id]);
            if (err) {
                res.apiError(API_CODE_SERVER_ERR, err);
                return;
            }
            console.log(`音频列表:${result.length}`);
            let audioList = result;
            if (result.length) {
                const ids = result.map(item => {
                    return item.audio_id;
                });
                const idsStirng = ids.join(',');
                const storeSql = ' SELECT `audio_id`, `category_id`, `is_like` FROM `audio_device` WHERE `uuid` = ? AND `audio_id` IN ( ' + idsStirng + ' )';
                const ret = await exesql(storeSql, [uuid]);
                if (ret.err) {
                    res.apiError(API_CODE_SERVER_ERR, ret.err.stack);
                    return;
                }
                let map = new Map();
                ret.result.forEach(item => {
                    if (item.is_like) {
                        map.set(LIKE_PREFIX + item.audio_id, item.category_id)
                    } else {
                        map.set(item.audio_id, item.category_id);
                    }
                });
                if (map.size) {
                    audioList = result.map(item => {
                        // 是否收藏
                        if (map.has(LIKE_PREFIX + item.audio_id)) {
                            Object.assign(item, { is_like: 1, uuid: uuid, favorite_category_id: item.category_id });
                        } else {
                            Object.assign(item, { is_like: null });
                        }
                        // 是否添加
                        if (map.has(item.audio_id)) {
                            Object.assign(item, { uuid: uuid, store_category_id: map.get(item.audio_id) });
                        } else {
                            Object.assign(item, { uuid: '' });
                        }
                        return item;
                    });
                }
            }
            if (count === 0 && audioList.length) {
                const { err, result } = await exesql('SELECT COUNT(*) as count FROM audio WHERE audio.`album_id` = ?', [album_id]);
                if (err) {
                    res.apiError(API_CODE_SERVER_ERR, err.stack);
                    return;
                }
                if (result.length) {
                    audioList = audioList.map(item => {
                        return Object.assign(item, { count: result[0].count })
                    })
                }
            }
            res.apiSuccess(audioList);

        } catch (error) {
            sendLogToEmail(error.stack);
            res.apiError(API_CODE_SERVER_ERR, error.stack);
        }
    }

    async getUserInfo(req, res) {
        const { device_sn } = req.body;
        const sql = SqlString.format('SELECT device.nick_name, device.avatar_url as avatar, device.gender, device.brithday FROM device WHERE device.sn = ?;', [device_sn]);
        try {
            const { err, result } = await exesql(sql);
            if (err) {
                res.apiError(API_CODE_SERVER_ERR, err);
                return;
            }
            if (result.length > 0) {
                res.apiSuccess(result[0]);
            } else {
                res.apiSuccess();
            }
        } catch (error) {
            res.apiError(API_CODE_SERVER_ERR, error.stack);
        }
    }

    async getAlbumInfo(req, res) {
        const { album_id } = req.body;
        const sql = SqlString.format('SELECT album.album_id,album.name,album.img_url,album.play_rate from album where album.album_id = ?;', [album_id]);
        try {
            const { err, result } = await exesql(sql);
            if (err) {
                res.apiError(API_CODE_SERVER_ERR, err);
                return;
            }
            res.apiSuccess(result);
        } catch (error) {
            res.apiError(API_CODE_SERVER_ERR, error.stack);
        }
    }

    // 添加专辑列表
    async addAlbum(req, res) {
        const { category_id, album_id, uuid } = req.body;
        try {
            const { err } = await exesql('call albumDeviceUpdate(?,?,?,?,?)', [category_id, album_id, uuid, 1, 1]);
            if (err) {
                res.apiError(API_CODE_SERVER_ERR, err);
                return;
            }
            res.apiSuccess();
        } catch (error) {
            res.apiError(API_CODE_SERVER_ERR, error.stack);
        }
    }

    // 移除添加的专辑
    async removeAlbum(req, res) {
        const { album_id, category_id, uuid } = req.body;
        try {
            const { err } = await exesql('call albumDeviceUpdate(?,?,?,?,?)', [category_id, album_id, uuid, 0, 1]);
            if (err) {
                res.apiError(API_CODE_SERVER_ERR, err);
                return;
            }
            res.apiSuccess();
        } catch (error) {
            res.apiError(API_CODE_SERVER_ERR, error.stack);
        }
    }

    // 添加单个音频
    async addAudio(req, res) {
        const { category_id, audio_id, uuid } = req.body;
        try {
            const { err, result } = await exesql('call audioDeviceUpdate(?,?,?,?,?)', [category_id, audio_id, uuid, 1, 1]);
            if (err) {
                res.apiError(API_CODE_SERVER_ERR, err);
                return;
            }
            res.apiSuccess();
        } catch (error) {
            res.apiError(API_CODE_SERVER_ERR, error.stack);
        }
    }

    // 添加单个音频
    async removeAudio(req, res) {
        const { audio_id, category_id, uuid } = req.body;
        try {
            const { err, result } = await exesql('call audioDeviceUpdate(?,?,?,?,?)', [category_id, audio_id, uuid, 0, 1]);
            if (err) {
                res.apiError(API_CODE_SERVER_ERR, err);
                return;
            }
            res.apiSuccess();
        } catch (error) {
            res.apiError(API_CODE_SERVER_ERR, error.stack);
        }
    }


    // 收藏单个音频
    async storeAudio(req, res) {
        const { category_id, audio_id, uuid } = req.body;
        try {
            const { err, result } = await exesql('call audioDeviceUpdate(?,?,?,?,?)', [category_id, audio_id, uuid, 1, 2]);
            if (err) {
                res.apiError(API_CODE_SERVER_ERR, err);
                return;
            }
            res.apiSuccess();
        } catch (error) {
            res.apiError(API_CODE_SERVER_ERR, error.stack);
        }
    }

    // 移除收藏
    async removeStoreAudio(req, res) {
        const { audio_id, category_id, uuid } = req.body;
        try {
            const { err, result } = await exesql('call audioDeviceUpdate(?,?,?,?,?)', [category_id, audio_id, uuid, 0, 2]);
            if (err) {
                res.apiError(API_CODE_SERVER_ERR, err);
                return;
            }
            res.apiSuccess();
        } catch (error) {
            res.apiError(API_CODE_SERVER_ERR, error.stack);
        }
    }

    async favoriteCount(req, res) {
        // id 为设备记录id
        const uuid = req.body.uuid;
        const sql = 'SELECT COUNT(*) as count FROM audio_device WHERE is_like = 1 and uuid = ?';
        try {
            const { err, result } = await exesql(sql, [uuid]);
            if (err) {
                sendLogToEmail(err);
                res.apiError(API_CODE_SERVER_ERR, err);
                return;
            }
            if (result.length === 0) {
                res.apiSuccess(0);
                return;
            }
            res.apiSuccess(result[0].count);
        } catch (error) {
            sendLogToEmail(error);
            res.apiError(API_CODE_SERVER_ERR, error.stack);
        }
    }

    // 统计类别个数
    async categoryCount(req, res) {
        const { uuid, range } = req.body;
        const sql = "select audio_device.category_id, count(*) as count from audio,audio_device where  audio.audio_id = audio_device.audio_id and audio_device.uuid=? AND audio_device.category_id IN (" + range + ") group by audio_device.category_id;";
        try {
            const { err, result } = await exesql(sql, [uuid]);
            if (err) {
                sendLogToEmail(err);
                res.apiError(API_CODE_SERVER_ERR, err);
                return;
            }
            res.apiSuccess(result);
        } catch (error) {
            sendLogToEmail(error);
            res.apiError(API_CODE_SERVER_ERR, error.stack);
        }
    }

    // 我的最爱列表
    async favoriteList(req, res) {
        const { uuid, id } = req.body;
        const sql = "SELECT audio_device.id,audio.album_id,audio_device.audio_id,audio.name,audio.duration,album.img_url, album.name as album_name FROM audio_device ,audio LEFT JOIN album ON (audio.album_id = album.album_id)  WHERE  audio_device.uuid = ? AND  audio_device.is_like = 1 AND  audio_device.audio_id = audio.audio_id  AND audio_device.id > ? ORDER BY audio_device.id LIMIT 20;"
        try {
            const { err, result } = await exesql(sql, [uuid, id]);
            if (err) {
                sendLogToEmail(err);
                res.apiError(API_CODE_SERVER_ERR, err);
                return;
            }
            res.apiSuccess(result);
        } catch (error) {
            sendLogToEmail(error);
            res.apiError(API_CODE_SERVER_ERR, error.stack);
        }
    }

    // 收藏的类别音频列表
    async storeCategoryAudioList(req, res) {
        const { uuid, category_id, latest_id } = req.body;
        const sql = "SELECT audio.audio_id,audio.name,audio.duration, album.album_id,album.name as album_name, album.img_url,audio_device.id FROM audio,album,audio_device  WHERE audio_device.uuid = ? AND audio_device.category_id = ? AND audio.album_id = album.album_id AND audio_device.audio_id = audio.audio_id AND audio_device.id > ? ORDER BY audio_device.id ASC LIMIT 20;";
        try {
            const { err, result } = await exesql(sql, [uuid, category_id, latest_id]);
            if (err) {
                sendLogToEmail(err);
                res.apiError(API_CODE_SERVER_ERR, err);
                return;
            }
            res.apiSuccess(result);
        } catch (error) {
            sendLogToEmail(error);
            res.apiError(API_CODE_SERVER_ERR, error.stack);
        }
    }

    // 查找指定类别下的音频列表
    async findCategoryAudioList(req, res) {
        const { uuid, category_id } = req.body;
        const sql = 'SELECT audio.audio_id,audio.name,audio.duration, album.album_id,album.name as album_name, album.img_url,audio_device.id FROM audio,album,audio_device  WHERE audio_device.uuid = ? AND audio_device.category_id = ? AND audio.album_id = album.album_id AND audio_device.audio_id = audio.audio_id;'
        try {
            const { err, result } = await exesql(sql, [uuid, category_id]);
            if (err) {
                sendLogToEmail(err);
                res.apiError(API_CODE_SERVER_ERR, err);
                return;
            }
            res.apiSuccess(result);
        } catch (error) {
            sendLogToEmail(error);
            res.apiError(API_CODE_SERVER_ERR, error.stack);
        }
    }


    // 删除我的收藏音频
    async removeFavorite(req, res) {
        const { uuid } = req.body;
        try {
            const { err, result } = await exesql('DELETE FROM `audio_device` WHERE `uuid`= ? AND `is_like`=1', [uuid]);
            if (err) {
                sendLogToEmail(err);
                res.apiError(API_CODE_SERVER_ERR, err);
                return;
            }
            res.apiSuccess(result);
        } catch (error) {
            sendLogToEmail(error);
            res.apiError(API_CODE_SERVER_ERR, error.stack);
        }
    }

    // 删除我的收藏类下的音频
    async removeStoreCategory(req, res) {
        const { uuid, category_id } = req.body;
        try {
            const { err, result } = await exesql('DELETE FROM `audio_device` WHERE `uuid`= ? AND `category_id` = ? ', [uuid, category_id]);
            if (err) {
                sendLogToEmail(err);
                res.apiError(API_CODE_SERVER_ERR, err);
                return;
            }
            res.apiSuccess(result);
        } catch (error) {
            sendLogToEmail(error);
            res.apiError(API_CODE_SERVER_ERR, error.stack);
        }
    }

}

export default new Common();